﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Drawing;
using System.Data;
using ERP_RORZE_SYSTEM;
using System.Data.OleDb;
using System.Data.Common;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace ERP_RORZE_DLL.Assembly
{
    public partial class panel_F5ProducingDetails : UserControl
    {
        private INISettings mySetting;
        private SqlConnection myConnection;
        private DataTable ExportTable;
        private DataTable SourceTable;
        public panel_F5ProducingDetails()
        {
            InitializeComponent();
            mySetting = new INISettings();
            myConnection = new SqlConnection(mySetting.SQLConnectionString);
            this.monthView.DateTime = DateTime.Now;
            this.ExportTable = new DataTable();
            this.SourceTable = new DataTable();

            this.buttonExport.Enabled = false;
        }
                        
        //////////////////////////////////////////////////
        /// Load data from database to grid
        //////////////////////////////////////////////////
        private void LoadData()
        {
            if (string.IsNullOrEmpty(this.monthView.EditValue.ToString()))
            {
                MessageBox.Show("Please select Month");
            }
            else
            {
                DataSet sourceDataSet = new DataSet();
                SQLManagement mySQLManagement = new SQLManagement();
                mySQLManagement.SQLConnectionString = mySetting.SQLConnectionString;
                string sql = "SELECT [CompID], [SerialNo], [MACAddress], [AttachSerial], [OrderNo], [ROMVersion],"
                    + " [Version], CheckPerson, CONVERT(NVARCHAR(10), FinishDate, 111) AS FinishDate,"
                    + " CONVERT(VARCHAR(10), ExportDate, 111) AS ExportDate, CustomerID, Remark, RoHS"
                    + " FROM [231_F5Detail]"
                    + " WHERE [CompID] IN (SELECT PartNo FROM [231_F5Plan]"
                    + " WHERE SUBSTRING(CONVERT(NVARCHAR(8), [FinishedDate], 112), 1, 6) = '" + this.monthView.DateTime.ToString("yyyyMM") + "')"
                    + " ORDER BY [CompID], [SerialNo], [FinishDate]";
                sourceDataSet = mySQLManagement.SQL_Query(sql);
                if (sourceDataSet != null)
                {
                    gridF5ProducingDetails.DataSource = sourceDataSet.Tables[0];
                    this.SourceTable = sourceDataSet.Tables[0].Copy();
                    this.ExportTable = this.SourceTable.Clone();
                }
                else
                {
                    DataTable newTable = new DataTable();
                    newTable.Columns.Add("CompID", typeof(string));
                    newTable.Columns.Add("SerialNo", typeof(string));
                    newTable.Columns.Add("MACAddress", typeof(string));
                    newTable.Columns.Add("AttachSerial", typeof(string));
                    newTable.Columns.Add("OrderNo", typeof(string));
                    newTable.Columns.Add("ROMVersion", typeof(string));
                    newTable.Columns.Add("Version", typeof(string));
                    newTable.Columns.Add("CheckPerson", typeof(string));
                    newTable.Columns.Add("FinishDate", typeof(string));
                    newTable.Columns.Add("ExportDate", typeof(string));
                    newTable.Columns.Add("CustomerID", typeof(string));
                    newTable.Columns.Add("Remark", typeof(string));
                    newTable.Columns.Add("RoHS", typeof(string));
                    gridF5ProducingDetails.DataSource = newTable;
                }
                F5ProducingDetails.Columns[0].Caption = "Comp ID";
                F5ProducingDetails.Columns[1].Caption = "Serial No";
                F5ProducingDetails.Columns[2].Caption = "MAC Address";
                F5ProducingDetails.Columns[3].Caption = "Attach Serial";
                F5ProducingDetails.Columns[4].Caption = "Order No";
                F5ProducingDetails.Columns[5].Caption = "ROM Version";
                F5ProducingDetails.Columns[6].Caption = "Loader Version";
                F5ProducingDetails.Columns[7].Caption = "Ins By";
                F5ProducingDetails.Columns[8].Caption = "Finish Date";
                F5ProducingDetails.Columns[9].Caption = "Export Date";
                F5ProducingDetails.Columns[10].Caption = "Customer";
                F5ProducingDetails.Columns[11].Caption = "Remark";
                F5ProducingDetails.Columns[12].Caption = "RoHS";
                F5ProducingDetails.BestFitColumns();

                this.buttonExport.Enabled = true;
            }
        }

        private void buttonView_Click(object sender, EventArgs e)
        {
            LoadData();
        }

        private void buttonExport_Click(object sender, EventArgs e)
        {
            this.ExportTable.Rows.Clear();

            Cursor currentCursor = Cursor.Current;
            Cursor.Current = Cursors.WaitCursor;
            try
            {
                SaveFileDialog saveFDlg = new SaveFileDialog();
                saveFDlg.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments);
                saveFDlg.Filter = "Microsoft Office Excel 2003|*.xls";
                saveFDlg.Title = "Save As";
                saveFDlg.FileName = "F5ProductDetails.xls";
                if (saveFDlg.ShowDialog(this) == DialogResult.OK)
                {
                    if (!string.IsNullOrEmpty(saveFDlg.FileName))
                    {
                        int data_page = 1;
                        string file_name = saveFDlg.FileName.Substring(0, saveFDlg.FileName.Length - 4);
                        for (int i = 0; i < this.SourceTable.Rows.Count; i++)
                        {
                            this.ExportTable.ImportRow(this.SourceTable.Rows[i]);                           

                            if ((i+1) % 65534 == 0)
                            {
                                ExportData.Columns.Clear();
                                gridExportData.DataSource = null;
                                gridExportData.DataSource = this.ExportTable;

                                ExportData.OptionsPrint.PrintDetails = true;
                                ExportData.OptionsPrint.ExpandAllDetails = true;
                                ExportData.OptionsPrint.AutoWidth = false;
                                Font default_font = new Font("Times New Roman", 10);
                                ExportData.AppearancePrint.Row.Font = default_font;
                                ExportData.AppearancePrint.HeaderPanel.Font = default_font;
                                ExportData.OptionsPrint.UsePrintStyles = true;
                                ExportData.OptionsPrint.SplitCellPreviewAcrossPages = false;                                
                                DevExpress.XtraPrinting.XlsExportOptions myOptions = new DevExpress.XtraPrinting.XlsExportOptions();
                                myOptions.SheetName = "Data" + data_page.ToString();
                                myOptions.TextExportMode = DevExpress.XtraPrinting.TextExportMode.Text;
                                myOptions.ShowGridLines = true;                                
                                ExportData.ExportToXls(file_name + data_page.ToString() + ".xls", myOptions);

                                this.ExportTable.Rows.Clear();
                                data_page++;
                            }
                        }

                        if (this.ExportTable.Rows.Count > 0)
                        {
                            ExportData.Columns.Clear();
                            gridExportData.DataSource = null;
                            gridExportData.DataSource = this.ExportTable;

                            ExportData.OptionsPrint.PrintDetails = true;
                            ExportData.OptionsPrint.ExpandAllDetails = true;
                            ExportData.OptionsPrint.AutoWidth = false;
                            Font default_font = new Font("Times New Roman", 10);
                            ExportData.AppearancePrint.Row.Font = default_font;
                            ExportData.AppearancePrint.HeaderPanel.Font = default_font;
                            ExportData.OptionsPrint.UsePrintStyles = true;
                            ExportData.OptionsPrint.SplitCellPreviewAcrossPages = false;
                            //RV201F06.ExportToXlsx(saveFDlg.FileName);
                            DevExpress.XtraPrinting.XlsExportOptions myOptions = new DevExpress.XtraPrinting.XlsExportOptions();
                            myOptions.SheetName = "Data" + data_page.ToString();
                            myOptions.TextExportMode = DevExpress.XtraPrinting.TextExportMode.Text;
                            myOptions.ShowGridLines = true;
                            ExportData.ExportToXls(file_name + data_page.ToString() + ".xls", myOptions);

                            this.ExportTable.Rows.Clear();
                        }

                        MessageBox.Show("Export completed!");
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Warning!");
            }
            finally
            {
                Cursor.Current = currentCursor;
            }
        }
    }
}
